using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Resources;
using System.Reflection;
using System.Data.SqlClient;

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

using ClassBuilderWizard.Interfaces;

namespace ClassBuilderWizard.DefaultPlugins
{
    internal class SMOHelper
    {
        private Server server; //smo server
        private SqlConnection DbConnection;
        private ResourceManager _SqlToCLR;
        private ResourceManager _SqlToNullableCLR;
        private Parameters _Parameters;

        public SMOHelper(string conn, Parameters pluginParams)
        {
            this.DbConnection = new SqlConnection(conn);

            Assembly assm = Assembly.GetExecutingAssembly();
            this._Parameters = pluginParams;
            //if (pluginParams.CurrentProjectType == ProjectType.VB)
            //    this._SqlToCLR = new ResourceManager("ClassBuilderWizard.DefaultPlugins.CLRTypes_VB", assm);
            //else
            this._SqlToCLR = new ResourceManager("ClassBuilderWizard.DefaultPlugins.CLRTypes_CS", assm);
            this._SqlToNullableCLR = new ResourceManager("ClassBuilderWizard.DefaultPlugins.CLRNullTypes_CS", assm);
        }

        /// <summary>
        /// Get a list of columns from a database table
        /// </summary>
        /// <param name="tableName">The name of the database table</param>
        /// <returns>Description of each column in the database table</returns>
        public List<DatabaseColumn> TableColumnList(string tableName, string schemaName)
        {
            List<DatabaseColumn> lc = new List<DatabaseColumn>();
            this.Connect();
            //retrieve only the tables from the database
            this.server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Table), "IsSystemObject");
            Database d = this.server.Databases[this.DbConnection.Database];

            Table t = d.Tables[tableName, schemaName]; //get the table requested

            foreach (Microsoft.SqlServer.Management.Smo.Column c in t.Columns)
            {
                //iterate through the columns in the table
                DatabaseColumn col = ConvertColumn(c);
                lc.Add(col);
            }
            this.Disconnect();

            return lc;
        }
        /// <summary>
        /// Translate an SMO Column object to a DatabaseColumn object for the plugin
        /// </summary>
        /// <param name="c">An SMO Column object</param>
        /// <returns>A ClassBuilderWizard DatabaseColumn object</returns>
        private DatabaseColumn ConvertColumn(Column c)
        {
            DatabaseColumn col = new DatabaseColumn();

            col.Name = c.Name;
            //get the lower-case name of the sql type
            col.DatabaseTypeName = GetDatabaseTypeName(c);
            //get the full name of the database type (ex: varchar(50) or char(10))
            col.DatabaseTypeDeclaration = GetFullDatabaseTypeName(c);
            //get the .NET type
            if (c.Nullable && this._Parameters.UseDatabaseNulls)
                col.CLRType = Type.GetType(this._SqlToNullableCLR.GetString(string.Format("SQL{0}", col.DatabaseTypeName)));
            else
                col.CLRType = Type.GetType(this._SqlToCLR.GetString(string.Format("SQL{0}", col.DatabaseTypeName)));
            col.IsIdentity = c.Identity;
            col.IsNullable = c.Nullable;
            col.IsReadOnly = c.Computed || c.Identity || (c.DataType.Name == DataType.Timestamp.Name);
            col.MaxLength = c.DataType.MaximumLength;
            col.NumericPrecision = c.DataType.NumericPrecision;
            col.NumericScale = c.DataType.NumericScale;

            return col;
        }
        /// <summary>
        /// Get a database column's SQL type
        /// </summary>
        /// <param name="c">An SMO Column object</param>
        /// <returns>The lower-case sql type name</returns>
        private string GetDatabaseTypeName(Column c)
        {
            string result = string.Empty;

            //get the base data type name
            if (c.DataType == null || c.DataType.ToString() == string.Empty)
            {
                result = c.DataType.SqlDataType.ToString().ToLower();
            }
            else
            {
                result = c.DataType.ToString().ToLower();
            }

            return result;
        }
        /// <summary>
        /// Get a database column's full SQL type including length
        /// </summary>
        /// <param name="c">An SMO Column object</param>
        /// <returns>The lower-case sql type name including length (example: varchar(50))</returns>
        private string GetFullDatabaseTypeName(Column c)
        {
            string result = GetDatabaseTypeName(c);

            //figure out if the data type name should be accented by a max lenght and/or precision
            if (result == "binary" || result == "char" || result == "nchar" || result == "nvarchar"
                || result == "varbinary" || result == "varchar")
            {
                //max length is needed
                string maxLength = (c.DataType.MaximumLength < 0) ? "MAX" : c.DataType.MaximumLength.ToString();
                result = string.Format("{0}({1})", result, maxLength);
            }
            else if (result == "decimal" || result == "numeric")
            {
                //need max length and precision
                result = string.Format("{0}({1},{2})", result, c.DataType.NumericPrecision, c.DataType.NumericScale);
            }

            return result;
        }
        /// <summary>
        /// Get a list of columns from a database view
        /// </summary>
        /// <param name="viewName">The name of the database view</param>
        /// <returns>Description of each column in the database view</returns>
        public List<DatabaseColumn> ViewColumnList(string viewName, string schema)
        {
            List<DatabaseColumn> lc = new List<DatabaseColumn>();
            this.Connect();
            //retrieve only the views from the database
            this.server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
            Database d = this.server.Databases[this.DbConnection.Database];

            View v = d.Views[viewName, schema]; //get the view requested

            foreach (Microsoft.SqlServer.Management.Smo.Column c in v.Columns)
            {
                //iterate through the columns in the table
                DatabaseColumn col = ConvertColumn(c);
                lc.Add(col);
            }
            this.Disconnect();

            return lc;
        }
        /// <summary>
        /// Get a list of tables in the database
        /// </summary>
        /// <returns>An array of table names</returns>
        public string[] TableList()
        {
            ArrayList al = new ArrayList();
            this.Connect();
            //limit smo call to only tables
            this.server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Table), "IsSystemObject");
            Database d = this.server.Databases[this.DbConnection.Database];

            //iterate through each of the tables
            foreach (Table t in d.Tables)
            {
                if (!t.IsSystemObject) //not a system table
                    al.Add(t.Schema + "." + t.Name);
            }
            this.Disconnect();

            return (string[])al.ToArray(typeof(string));
        }
        /// <summary>
        /// Get a list of views in the database
        /// </summary>
        /// <returns>An array of view names</returns>
        public string[] ViewList()
        {
            ArrayList al = new ArrayList();
            this.Connect();
            //limit to only views
            this.server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.View), "IsSystemObject");
            Database d = this.server.Databases[this.DbConnection.Database];

            //iterate through each of the views in the database
            foreach (View v in d.Views)
            {
                if (!v.IsSystemObject)
                    al.Add(v.Schema + "." + v.Name);
            }
            this.Disconnect();

            return (string[])al.ToArray(typeof(string));
        }
        /// <summary>
        /// Disconnects the SMO connection from the database server
        /// </summary>
        private void Disconnect()
        {
            if (this.DbConnection.State != System.Data.ConnectionState.Closed)
            {
                //close the sql connection
                this.DbConnection.Close();
            }
        }
        /// <summary>
        /// Connect the SMO connection to the database server
        /// </summary>
        private void Connect()
        {
            if (this.DbConnection.State != System.Data.ConnectionState.Open)
            {
                //open the sql connection and create the SMO server object
                this.DbConnection.Open();
                ServerConnection sconn = new ServerConnection(this.DbConnection);
                this.server = new Server(sconn);
            }
        }
    }
}
